package com.example.androidmaps;

import java.util.ArrayList;
import java.util.Date;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

public class Database {
	public static final String DATABASE_NAME = "MapApp";
	public static final int SCHEMA_VERSION = 1;
	
	//Database names and columns for user table 
	public static final String TABLE_USER = "User";
	public static final String USER_ID = "Id";
	public static final String CLOUD_ID = "CloudId";
	public static final String USERNAME = "Username";
	
	//query to create user table
	public static final String CREATE_DATABASE_USER = "" +
			"CREATE TABLE " + TABLE_USER + "("
				+ USER_ID + " integer primary key autoincrement,"
				+ CLOUD_ID + " integer NOT NULL,"
				+ USERNAME + " varchar(100) NOT NULL"
			+ ");";
	
	//Database names and columns for favorite places table 
	public static final String TABLE_PLACES = "Places";
	public static final String PLACE_ID = "Id";
	public static final String PLACE_CLOUD_ID = "CloudId";
	public static final String PLACE_USER_CLOUD_ID = "CloudUserId";
	public static final String PLACE_LON = "Lon";
	public static final String PLACE_LAT = "Lat";
	public static final String PLACE = "Place";
	public static final String CURRENT_LOCATION = "CurrentLocation";
	
	//query to create user table
	public static final String CREATE_DATABASE_PLACES = "" +
			"CREATE TABLE " + TABLE_PLACES + "("
				+ PLACE_ID + " integer primary key autoincrement,"
				+ PLACE_CLOUD_ID + " integer NOT NULL,"
				+ PLACE + " varchar(100) NOT NULL,"
				+ PLACE_LON + " double NOT NULL,"
				+ PLACE_LAT + " double NOT NULL,"
				+ CURRENT_LOCATION + " boolean default 0"
			+ ");";
	
	//Database names and columns for friends lists table 
	public static final String TABLE_FRIENDS = "Friends";
	public static final String FRIEND_ID = "Id";
	public static final String FRIEND_CLOUD_ID = "CloudId";
	public static final String CLOUD_USER_ID = "UserCloudId";
	public static final String CLOUD_USERNAME = "CloudUsername";
	public static final String APPROVED = "Approved";
	public static final String FRIEND_CURRENT_LOCATION = "CurrentLocation";
	
	public static final String CREATE_DATABASE_FRIENDS = "" +
		"CREATE TABLE " + TABLE_FRIENDS + "("
			+ FRIEND_ID + " integer primary key autoincrement,"
			+ FRIEND_CLOUD_ID + " integer NOT NULL,"
			+ CLOUD_USER_ID + " integer NOT NULL,"
			+ CLOUD_USERNAME + " varchar(100) NOT NULL,"
			+ APPROVED + " boolean default 0,"
			+ FRIEND_CURRENT_LOCATION+ " varchar(100) NOT NULL"
		+ ");";
	
	private Context context;
	private DbHelper helper;
	private SQLiteDatabase database;
	
	private final class DbHelper extends SQLiteOpenHelper{

		public DbHelper(Context context) {
			super(context, DATABASE_NAME, null, SCHEMA_VERSION);
			// TODO Auto-generated constructor stub
		}

		@Override
		public void onCreate(SQLiteDatabase db) {
			// TODO Auto-generated method stub
			db.execSQL(CREATE_DATABASE_PLACES);
			db.execSQL(CREATE_DATABASE_FRIENDS);
			db.execSQL(CREATE_DATABASE_USER);
		}

		@Override
		public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
			// TODO Auto-generated method stub
			db.execSQL("DROP TABLE IF EXISTS " + TABLE_PLACES);
			db.execSQL("DROP TABLE IF EXISTS " + TABLE_FRIENDS);
			db.execSQL("DROP TABLE IF EXISTS " + TABLE_USER);
			onCreate(db);
		}
	}
	
	public Database(Context c){
		context = c;
	}
	
	public Database open(){
		helper= new DbHelper(context);
		database = helper.getWritableDatabase();
		return this;
	}
	
	public void close(){
		helper.close();
	}
	
	/*
	 * /////////////////////////////////
	 * IMPLEMENT DATABASE METHODS HERE
	 * /////////////////////////////////
	 */
	
	/*
	 * Checks to see if any user is currently logged in, if so return user, else return null
	 */
	public User CheckUserLoggedIn(){
		Cursor cursor = database.query(TABLE_USER, new String[] {USER_ID, CLOUD_ID, USERNAME}, null, null, null, null, null);
		cursor.moveToFirst();
		Date nowDate = new Date(System.currentTimeMillis());
		Date checkDate;
		User user = new User();
		if(cursor.getCount() == 0){
			cursor.close();
			database.close();
			return null;
		}
		else{
			user.Id = cursor.getInt(cursor.getColumnIndex(USER_ID));
			user.CloudId = cursor.getInt(cursor.getColumnIndex(CLOUD_ID));
			user.Username = cursor.getString(cursor.getColumnIndex(USERNAME));
			return user;
		}
	}
	
	public void LogOutUser(){
		database.delete(TABLE_USER, null,null);
	}
	
	//Inserts a new logged in user to the database
	public void InsertNewCloudUser(int cloudId, String username){
		//remove previous user
		LogOutUser();
		ContentValues values = new ContentValues();
		values.put(CLOUD_ID, cloudId);
		values.put(USERNAME, username);

		// Inserting Row
		database.insert(TABLE_USER, null, values);
		database.close(); // Closing database connection
	}
	
	public void InsertFavoritePlace(int cloudId, int cloudUserId, double lon, double lat, String place, boolean current){
		//remove previous user
		LogOutUser();
		ContentValues values = new ContentValues();
		values.put(PLACE_CLOUD_ID, cloudId);
		values.put(PLACE_USER_CLOUD_ID, cloudUserId);
		values.put(PLACE_LON, lon);
		values.put(PLACE_LAT, lat);
		values.put(PLACE, place);
		values.put(FRIEND_CURRENT_LOCATION, current);

		// Inserting Row
		database.insert(TABLE_PLACES, null, values);
		database.close(); // Closing database connection
	}
	
	public ArrayList<Friend> GetAllFriends(){
		ArrayList<Friend> friends = new ArrayList<Friend>();
		String selectQuery = "SELECT * FROM " + TABLE_FRIENDS;
		Cursor cursor = database.rawQuery(selectQuery, null);
		if (cursor.moveToFirst()) {
			do {
				Friend f = new Friend();
				f.CloudId = (Integer.parseInt(cursor.getString(1)));
				f.CloudUserId = (Integer.parseInt(cursor.getString(2)));
				f.CloudUsername =(cursor.getString(3));
				f.Approved = (Integer.parseInt((cursor.getString(4))) == 0 ? false : true);
				f.CurrentLocation = (cursor.getString(5));
				friends.add(f);
			} while (cursor.moveToNext());
		}

		cursor.close();
		database.close();
		
		return friends;
	}
	
	public void InsertFriend(Friend f){
		ContentValues values = new ContentValues();
		
		values.put(FRIEND_ID, f.CloudId);
		values.put(CLOUD_USER_ID, f.CloudUserId);
		values.put(CLOUD_USERNAME, f.CloudUsername);
		values.put(APPROVED, f.Approved);
		values.put(FRIEND_CURRENT_LOCATION, f.CurrentLocation);
		
		database.insert(TABLE_FRIENDS, null, values);
		database.close();
	}
}
